﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class LateAbsenceController
    {
        private DBConnector connector;

        public LateAbsenceController()
        {
            connector = new DBConnector();
        }

        public DataTable getData(String Fname, String Lname)
        {
            string query = "SELECT periodStart as 'Start Date', periodEnd as 'End Date', late as 'Late', absences as 'Absences' from period p inner join attendance a inner join employee e where p.periodID = a.periodID and e.employeecode = a.employeecode and e.lastname= '" + Fname + "' and e.firstname = '"+ Lname +"' ORDER BY periodStart DESC;";

            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
            {
                return null;
            }
        }

        public void updateAttendance(string firstname, string lastname)
        {
            MiscChargeController miscCharge = new MiscChargeController();
            EmployeeController employeeControl = new EmployeeController();
            string query = "INSERT INTO attendance (employeeCode, periodID, late, absences) VALUES (" + employeeControl.getEmployeeCode(firstname,lastname) + ", " + miscCharge.getPeriodID() + ",0,0)";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                }
                catch (Exception ex)
                {
                    //MessageBox.Show("An error has occurred.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    MessageBox.Show(ex.Message);
                }
            }
        }

        public void updateAttendance(int employeeCode)
        {
            MiscChargeController miscCharge = new MiscChargeController();
            string query = "INSERT INTO attendance (employeeCode, periodID, late, absences) VALUES (" + employeeCode + ", " + miscCharge.getPeriodID() + ",0,0)";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("An error has occurred.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        public bool addAbsent(String Lname, String Fname, attendance absent)
        {
            EmployeeController empControl = new EmployeeController();
            MiscChargeController miscCharge = new MiscChargeController();
            int total = getCurrentAbsences(Lname, Fname) + absent.getValue();
            string query = "UPDATE attendance SET absences = "+total+" WHERE employeeCode = "+empControl.getEmployeeCode(Fname,Lname)+" AND periodID = "+miscCharge.getPeriodID();
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            return false;
        }

        public int getCurrentAbsences(string lname, string fname)
        {
            int absences =0;
            EmployeeController empControl = new EmployeeController();
            MiscChargeController miscCharge = new MiscChargeController();
            string query = "SELECT absences from attendance where employeeCode = "+empControl.getEmployeeCode(fname,lname)+" AND periodID = "+miscCharge.getPeriodID();
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    absences = (int)dataReader["absences"];
                dataReader.Close();
                connector.closeConnection();
            }
            return absences;
        }

        public bool addLates(String Lname, String Fname, attendance late)
        {
            EmployeeController empControl = new EmployeeController();
            MiscChargeController miscCharge = new MiscChargeController();
            int total = getCurrentLate(Lname, Fname) + late.getValue();
            string query = "UPDATE attendance SET late = " + total + " WHERE employeeCode = " + empControl.getEmployeeCode(Fname, Lname) + " AND periodID = " + miscCharge.getPeriodID();
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            return false;
        }

        public int getCurrentLate(string lname, string fname)
        {
            int lates = 0;
            EmployeeController empControl = new EmployeeController();
            MiscChargeController miscCharge = new MiscChargeController();
            string query = "SELECT late from attendance where employeeCode = "+empControl.getEmployeeCode(fname,lname)+" AND periodID = "+miscCharge.getPeriodID();
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    lates = (int)dataReader["late"];
                dataReader.Close();
                connector.closeConnection();
            }
            return lates;
        }

        public bool editAbsences(attendance absent, String Lname, String Fname, String date)
        {
            string query = "UPDATE attendance SET absences = " + absent.getValue() + " WHERE employeeCode = (SELECT employeeCode FROM employee where lastName= \'" + Lname + "\' and firstName = \'" + Fname + "\') and periodID =(select periodID from period where periodStart= \'" + date + "\');";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Problem in editing the data. Date is " + date, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }

        public bool editLates(attendance late, String Lname, String Fname, String date)
        {
            string query = "UPDATE attendance SET late = " + late.getValue() + " WHERE employeeCode = (SELECT employeeCode FROM employee WHERE lastName= \'" + Lname + "\' and firstName = \'" + Fname + "\') and periodID = (SELECT periodID FROM period WHERE periodStart= \'" + date + "\');";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("problem editing the data date is " + date, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }

        public bool deleteAbsences(String Lname, String Fname, String date)
        {
            string query = "UPDATE attendance SET absences = 0 WHERE employeeCode = (SELECT employeeCode FROM employee WHERE lastName= \'" + Lname + "\' and firstName = \'" + Fname + "\') and periodID = (SELECT periodID FROM period WHERE periodStart= \'" + date + "\');";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Problem in editing the data. Date is " + date, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }
        public bool deleteLates(String Lname, String Fname, String date)
        {
            string query = "UPDATE attendance SET late = 0 WHERE employeeCode = (SELECT employeeCode FROM employee WHERE lastName= \'" + Lname + "\' and firstName = \'" + Fname + "\') and periodID = (SELECT periodID FROM period WHERE periodStart= \'" + date + "\');";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Problem editing the data. Date is " + date, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }
    }
}
